AllowZeroLength Property Example

In this example, the AllowZeroLength property allows the user to set the value of a Field to an empty string. In this situation, the user can distinguish between a record where data is not known and a record where the data does not apply.

Sub AllowZeroLengthX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim fldTemp As Field
    Dim rstEmployees As Recordset
    Dim strMessage As String
    Dim strInput As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind.TableDefs("Employees")
    ' Create a new Field object and append it to the Fields 
    ' collection of the Employees table.
    Set fldTemp = tdfEmployees.CreateField("FaxPhone", _
        dbText, 24)
    fldTemp.AllowZeroLength = True
    tdfEmployees.Fields.Append fldTemp

    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        ' Get user input.
        .Edit
        strMessage = "Enter fax number for " & _
            !FirstName & " " & !LastName & "." & vbCr & _
            "[? - unknown, X - has no fax]"
        strInput = UCase(InputBox(strMessage))
        If strInput <> "" Then
            Select Case strInput
                Case "?"
                    !FaxPhone = Null
                Case "X"
                    !FaxPhone = ""
                Case Else
                    !FaxPhone = strInput
            End Select

            .Update

            ' Print report.
            Debug.Print "Name - Fax number"
            Debug.Print !FirstName & " " & !LastName & " - ";

            If IsNull(!FaxPhone) Then
                Debug.Print "[Unknown]"
            Else
                If !FaxPhone = "" Then
                    Debug.Print "[Has no fax]"
                Else
                    Debug.Print !FaxPhone
                End If
            End If

        Else
            .CancelUpdate
        End If

        .Close
    End With

    ' Delete new field because this is a demonstration.
    tdfEmployees.Fields.Delete fldTemp.Name
    dbsNorthwind.Close

End Sub